<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="Account" 
	xmlns="http://ibatis.apache.org/mapping" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >

	<alias>
		<typeAlias alias="HundredsBool" type="MyBatis.DataMapper.SqlClient.Test.Domain.HundredsTypeHandlerCallback, MyBatis.DataMapper.SqlClient.Test"/>
		<typeAlias alias="Category2" type="MyBatis.DataMapper.SqlClient.Test.Domain.Category, MyBatis.DataMapper.SqlClient.Test"/>
	</alias>       
  
  <cacheModels>
    <cacheModel id="account-cache" type="Perpetual" flushInterval="30" >
			<flushOnExecute  statement="UpdateAccountViaInlineParameters"/>
			<flushOnExecute  statement="UpdateAccountViaParameterMap"/>
		</cacheModel>
		
		<cacheModel id="rw-account-cache" type="Fifo" flushInterval="600" share="true">
			<flushOnExecute statement="UpdateAccountViaInlineParameters"/>
		</cacheModel>
		
    </cacheModels>

	
	<resultMaps>

    <resultMap id="account-constructor"  class="Account" >
      <constructor>
        <argument argumentName="identifiant"	column="Account_ID"/>
        <argument argumentName="firstName"    column="Account_FirstName"/>
        <argument argumentName="lastName"     column="Account_LastName"/>
      </constructor>
    </resultMap>
    <resultMap id="account-extends-constructor" class="Account" extends="account-constructor" >
      <result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
      <result property="BannerOption" column="Account_Banner_Option" dbType="Varchar" type="bool"/>
      <result property="CartOption"	  column="Account_Cart_Option" typeHandler="HundredsBool"/>
    </resultMap>
    
    <resultMap id="email-result" class="string">
      <result property="value" column="Account_Email"/>
    </resultMap>
    
		<resultMap id="Category-result" class="Category2">
		  <result property="Id" column="Category_ID"/>
		  <result property="Name" column="Category_Name"/>
		  <result property="Guid" column="Category_Guid" nullValue="00000000-0000-0000-0000-000000000000"/>
		</resultMap>
    
		<resultMap id="account-result-constructor"  class="Account" >
			<constructor>
				<argument argumentName="identifiant"	column="Account_ID"/>
				<argument argumentName="firstName"    column="Account_FirstName"/>
				<argument argumentName="lastName"     column="Account_LastName"/>
			</constructor>
			<result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
			<result property="BannerOption" column="Account_Banner_Option" dbType="Varchar" type="bool"/>
			<result property="CartOption"	  column="Account_Cart_Option" typeHandler="HundredsBool"/>
		</resultMap>

		<resultMap id="account-groupby"  class="Account" groupBy="id">
			<result property="id"           column="Account_ID"/>
			<result property="FirstName"    column="Account_FirstName"/>
			<result property="documents" resultMapping="Document.document"/>
		</resultMap>

		<resultMap id="account-result"  class="Account" >
			<result property="id"           column="Account_ID"/>
			<result property="FirstName"    column="Account_FirstName"/>
			<result property="LastName"     column="Account_LastName"/>
			<result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
			<result property="BannerOption" column="Account_Banner_Option" dbType="Varchar" type="bool"/>
			<result property="CartOption"	column="Account_Cart_Option" typeHandler="HundredsBool"/>
		</resultMap>

    <resultMap id="account-result-joined-document"  class="Account" >
      <result property="id"           column="Account_ID"/>
      <result property="FirstName"    column="Account_FirstName"/>
      <result property="LastName"     column="Account_LastName"/>
      <result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
      <result property="BannerOption" column="Account_Banner_Option" dbType="Varchar" type="bool"/>
      <result property="CartOption"	column="Account_Cart_Option" typeHandler="HundredsBool"/>
      <result property="Document" resultMapping="Document.document"/>
    </resultMap>

    <resultMap id="account-result-nullable-email" class="Account">
      <result property="Id"           column="Account_ID"/>
      <result property="FirstName"    column="Account_FirstName"/>
      <result property="LastName"     column="Account_LastName"/>
      <result property="EmailAddress" column="Account_Email"/>
    </resultMap>

    <resultMap id="account-hashtable-result" class="Hashtable">
      <result property="Id"           column="Account_ID"/>
      <result property="FirstName"    column="Account_FirstName"/>
      <result property="LastName"     column="Account_LastName"/>
      <result property="EmailAddress" column="Account_Email"/>
    </resultMap>

    <resultMap id="indexed-account-result" class="Account">
      <result property="Id"           	columnIndex="0"/>
      <result property="FirstName"    	columnIndex="1"/>
      <result property="LastName"     	columnIndex="2"/>
      <result property="EmailAddress" 	columnIndex="3"	nullValue="no_email@provided.com"/>
    </resultMap>
    
	</resultMaps>


  <statements>

    <select id="JIRA206"
       resultMap="account-groupby">
      SELECT
      Accounts.Account_ID,
      Account_FirstName,
      Document_ID,
      Document_City,
      Document_PageNumber,
      Document_Type,
      Document_Title
      FROM         Accounts LEFT OUTER JOIN
      Documents ON Accounts.Account_ID = Documents.Account_Id
      Order by Accounts.Account_ID
    </select>
    
    <select id="GetMultipleResultMapAccount"
				resultMap="account-result, account-result">
      select
      Account_ID,
      Account_FirstName,
      Account_LastName,
      Account_Email,
      Account_Banner_Option,
      Account_Cart_Option
      from Accounts
      where Account_ID = 1;
      
      select
      Account_ID,
      Account_FirstName,
      Account_LastName,
      Account_Email,
      Account_Banner_Option,
      Account_Cart_Option
      from Accounts
      where Account_ID = 5
    </select>

    <select id="GetMultipleResultClassAccount" resultClass="Account, Account" >
      select
      Account_ID as Id,
      Account_FirstName as FirstName,
      Account_LastName as LastName,
      Account_Email as EmailAddress,
      Account_Cart_Option as CartOption
      from Accounts
      where Account_ID = 1;

      select
      Account_ID as Id,
      Account_FirstName as FirstName,
      Account_LastName as LastName,
      Account_Email as EmailAddress,
      Account_Cart_Option as CartOption
      from Accounts
      where Account_ID = 5
    </select >

    <select id="GetMultipleResultMap"
      resultMap="account-result, Category-result">
      select
      Account_ID,
      Account_FirstName,
      Account_LastName,
      Account_Email,
      Account_Banner_Option,
      Account_Cart_Option
      from Accounts
      where Account_ID = 1;

      select
      Category_ID,
      Category_Name,
      Category_Guid
      from Categories
      where Category_ID = 1
    </select>


    <select id="GetMultipleResultClass" resultClass="Account, Category2" >
      select
      Account_ID as Id,
      Account_FirstName as FirstName,
      Account_LastName as LastName,
      Account_Email as EmailAddress,
      Account_Cart_Option as CartOption
      from Accounts
      where Account_ID = 1;

      select
      Category_ID,
      Category_Name as Name,
      Category_Guid as Guid
      from Categories
      where Category_ID = 1
    </select >

    <select id="JIRA175" parameterClass="Integer"
         resultMap="account-result-joined-document">
      select *
      from Accounts a, Documents d
      where a.Account_ID = d.Account_ID
      And a.Account_ID = #value#
    </select>
    
	  <select id="SelectAccountConstructor" parameterClass="Integer"
					resultMap="account-result-constructor">
			select *
			from Accounts
			where Account_ID = #value#
		</select>
		
		<select id="GetAccountsDynamic" resultMap="account-result" parameterClass="Hashtable" >
			select top $MaximumAllowed$ * from Accounts
			<dynamic prepend="where">
					<isParameterPresent>
					<isNotEmpty prepend="and" property="FirstName" >
							Account_FirstName LIKE '%$FirstName$%'
					</isNotEmpty>
					<isNotEmpty prepend="and" property="LastName" >
							Account_LastName LIKE '%$LastName$%'
					</isNotEmpty>
					<isNotEmpty prepend="and" property="EmailAddress"  >
							Account_Email LIKE '%$EmailAddress$%'
					</isNotEmpty>
					</isParameterPresent>
				</dynamic>
				order by Account_LastName
		</select>

		<select id="GetCachedAccountsViaResultMap"
					resultMap="account-result"
					cacheModel="account-cache" >
			select *
			from Accounts
			order by Account_ID
		</select>
  
		<select id="GetRWCachedAccountsViaResultMap"
				resultMap="account-result"
				cacheModel="rw-account-cache" extends="GetCachedAccountsViaResultMap">
      where 1=1
		</select>
  
  		<select id="GetNoAccountWithCache"
						parameterClass="Integer"
						resultMap="account-result-nullable-email"
						cacheModel="account-cache">
			select *
			from Accounts
			where Account_ID = #value#
		</select>
		
  		<select id="SelectWithProperty"
					resultMap="account-result">
			select *
			from Accounts
			where Account_FirstName = ${accountName}
		</select>
		
		<select id="GetAccountAsHashtable"
						parameterClass="Integer"
						resultMap="account-hashtable-result">
			select *
			from Accounts
			where Account_ID = #value#
		</select>
  
		<select id="GetAllAccountsAsHashMapViaResultMap"
						resultMap="account-hashtable-result">
			select *
			from Accounts
			order by Account_ID
		</select>
  
		<select id="GetAccountAsHashtableResultClass"
						parameterClass="int"
						resultClass="HashMap">
			select
			Account_ID as Id,
			Account_FirstName as FirstName,
			Account_LastName as LastName,
			Account_Email as EmailAddress
			from Accounts
			where Account_ID = #value#
		</select>
  
		<select id="GetAllAccountsAsHashtableViaResultClass"
						resultClass="Hashtable">
			select
			Account_ID as Id,
			Account_FirstName as FirstName,
			Account_LastName as LastName,
			Account_Email as EmailAddress
			from Accounts
			order by Account_ID
		</select>

		<select id="GetAllAccountsAsArrayListViaResultClass"
						resultClass="ArrayList">
			select
			Account_ID as Id,
			Account_FirstName as FirstName,
			Account_LastName as LastName,
			Account_Email as EmailAddress
			from Accounts
			order by Account_ID
		</select>
    
		<select id="GetAccountViaColumnName"
				parameterClass="int"
				resultMap="account-result">
			select
			Account_ID,
			Account_FirstName,
			Account_LastName,
			Account_Email, 
			Account_Banner_Option,
			Account_Cart_Option
			from Accounts
			where Account_ID = #value#
		</select>
		
		<select id="GetAccountViaColumnIndex"
				parameterClass="int"
				resultMap="indexed-account-result">
			select
			Account_ID,
			Account_FirstName,
			Account_LastName,
			Account_Email
			from Accounts
			where Account_ID = #value#
		</select>
  
		<select id="GetAllAccountsViaResultMap"
					resultMap="account-result">
			select * from Accounts
			order by Account_ID
		</select>
				
		<select id="GetAllAccountsViaResultClass" 
			resultClass="Account">
			select
			Account_ID as id,
			Account_FirstName as FirstName,
			Account_LastName as LastName,
			Account_Email as EmailAddress
			from Accounts
			order by Account_ID
		</select>
		
		<select id="GetAllAccountsCache" 
			resultMap="account-result"
			cacheModel="account-cache">
			select * from Accounts
			order by Account_ID
		</select>
		
		<select id="GetFewAccountsViaResultMap"
			resultMap="account-result">
			<![CDATA[
			select * from Accounts
			where Account_ID < 2
			order by Account_ID
			]]>
		</select>
  
		<select id="GetNoAccountsViaResultMap"
					resultMap="account-result">
			select * from Accounts
			where Account_ID > 1000
			order by Account_ID
		</select>
  
		<select id="GetAccountNullableEmail" 
			resultMap="account-result-nullable-email">
			select
			Account_ID,
			Account_FirstName,
			Account_LastName,
			Account_Email
			from Accounts
			where Account_ID = #value#
		</select>
  
		<select id="GetAccountViaResultClass" 
			resultClass="Account">
			<![CDATA[
					select
					Account_ID as Id,
					Account_FirstName as FirstName,
					Account_LastName as LastName,
					Account_Email as EmailAddress
					from Accounts
					where Account_ID = #value#
			]]>
		</select>
		
		<select id="GetAccountViaInlineParameters"
				resultMap="indexed-account-result">
			select
			Account_ID,
			Account_FirstName,
			Account_LastName,
			Account_Email
			from Accounts
			where Account_ID = #Id# and Account_ID = #Id#
		</select>
		
		 <select id="GetEmailAddressViaResultClass" resultClass="string">
			select Account_Email as value
			from Accounts
			where Account_ID = #value#
		</select>
 
		<select id="GetEmailAddressViaResultMap"
						parameterClass="int"
						resultMap="email-result">
			select Account_Email
			from Accounts
			where Account_ID = #value#
		</select>

		<select id="GetAllEmailAddressesViaResultClass"
							resultClass="string">
			select Account_Email
			from Accounts
			order by Account_ID
		</select>
		
		 <select id="GetAllEmailAddressesViaResultMap"
                    resultMap="email-result">
			select Account_Email
			from Accounts
			order by Account_ID
		</select>
					
		 <insert id="InsertAccountViaParameterMap"
                    parameterMap="insert-params">
			insert into Accounts  
				(Account_ID, Account_FirstName, Account_LastName, Account_Email, Account_Banner_Option, Account_Cart_Option) 
			values 
				(?, ?, ?, ?, ?, ?)
		</insert>
		
		 <update id="UpdateAccountViaParameterMap"
                    parameterMap="update-params">
			update Accounts set
			Account_FirstName = ?,
			Account_LastName = ?,
			Account_Email = ?
			where
			Account_ID = ?
		</update>
		
		<update id="UpdateAccountViaParameterMap2"
                    parameterMap="update-params2">
			update Accounts set
			Account_ID = ?,
			Account_FirstName = ?,
			Account_LastName = ?,
			Account_Email = ?
			where
			Account_ID = ?
		</update>
				
		<delete id="DeleteAccountViaInlineParameters">
			delete from Accounts 
			where
			Account_ID = #Id#
		</delete>
		
		<select id="GetAccountComplexMapping"
					resultMap="indexed-account-result"
                    parameterClass="Hashtable">
			select *
			from Accounts 
			where 
			Account_FirstName = #Account.FirstName# 
			And Account_LastName = #Order.City#
		</select>
		
		<select id="GetDynamicOrderedEmailAddressesViaResultMap"
                    resultMap="email-result">
			select Account_Email
			from Accounts
			order by $value$
		</select>
		
		<!-- Dynamic statements -->
		<select id="GetAllAccountsViaResultMapWithDynamicElement"
				resultMap="account-result">
			select * from Accounts
			where Account_Email $value$ '%@%'
			order by Account_ID
		</select>
		
		<select id="SimpleDynamicSubstitution"
             parameterClass="Hashtable"
             resultClass="Account">
			$statement$
		</select>
		
		<!-- Public Fields -->
		<insert id="InsertAccountViaPublicFields">
			insert into Accounts 
				(Account_ID, Account_FirstName, Account_LastName, Account_Email) 
			values 
				(#Id#, #FirstName#, #LastName#, #EmailAddress#
			)
		</insert>
		

		<!-- Inline Parameters #EmailAddress:VarChar:no_email@provided.com#-->
		<update id="UpdateAccountViaInlineParameters"
			parameterClass="Account">
			update Accounts set
			Account_FirstName = #FirstName#,
			Account_LastName = #LastName#,
			Account_Email = #EmailAddress,type=string,dbType=Varchar,nullValue=no_email@provided.com#
			where
			Account_ID = #Id#
		</update>

		<insert id="InsertAccountViaInlineParameters"
			parameterClass="Account" >
			insert into Accounts 
				(Account_ID, Account_FirstName, Account_LastName, Account_Email) 
			values 
				(#Id#, #FirstName#, #LastName#, #EmailAddress:VarChar:no_email@provided.com#
			)
		</insert>

		<insert id="InsertAccountNullableEmail"
			parameterClass="Account" >
			insert into Accounts 
				(Account_ID, Account_FirstName, Account_LastName, Account_Email) 
			values 
				( #Id#, #FirstName#, #LastName#, #EmailAddress:VarChar# )
		</insert>

		<insert id="InsertAccountUknownParameterClass">
			insert into Accounts 
				(Account_ID, Account_FirstName, Account_LastName, Account_Email) 
			values 
				( #Id#, #FirstName#, #LastName#, #EmailAddress:VarChar# )
		</insert>
  
		<delete id="DeleteAccount"
			parameterClass="Account">
			delete from Accounts
			where Account_ID = #Id#
			and Account_ID = #Id#
		</delete>
		
		<!-- Extends statement -->
		<select id="GetAllAccounts"
					resultMap="indexed-account-result">
			select
			Account_ID,
			Account_FirstName,
			Account_LastName,
			Account_Email
			from Accounts
		</select>
		
		<select id="GetAllAccountsOrderByName"
			extends="GetAllAccounts"
			resultMap="indexed-account-result">
			order by Account_FirstName
		</select>
		
		<select id="GetOneAccount"
			extends="GetAllAccounts"
			resultMap="indexed-account-result">
			where Account_ID = #value#
		</select>
		
		<select id="GetSomeAccount"
			extends="GetAllAccounts"
			parameterClass="Hashtable"
			resultMap="indexed-account-result">
			where Account_ID between #lowID# and #hightID#
		</select>
		
		<select id="SelectAccountJIRA29" parameterClass="map" resultClass="Account">
			select
				Account_ID as Id,
				Account_FirstName as FirstName,
				Account_LastName as LastName,
				Account_Email as EmailAddress
			from Accounts
			where Account_FirstName = '##$AccountName$##'
		</select>	
			
		<select id="SelectAccountJIRA29-2"
				parameterClass="Hashtable"
				resultClass="Account">
			select
				Account_ID as Id,
				Account_FirstName as FirstName,
				Account_LastName as LastName,
				Account_Email as EmailAddress
			from Accounts
			where 1=1
			<isNotEmpty prepend="AND" property="Foo"> 
				(Account_FirstName = '##$Foo$##') 
			</isNotEmpty> 
		</select>	
		
		<select id="GetAccountWithRepeatingProperty"
						parameterClass="Account" 
						resultMap="indexed-account-result">
			select
					Account_ID,
					Account_FirstName,
					Account_LastName,
					Account_Email
			from Accounts
			where Account_ID = #Id# and 
					Account_ID = #Id# and 
					Account_FirstName = #FirstName# and 
					Account_LastName = #LastName# and 
					Account_ID = #Id#				
		</select>	
		
		<select id="GetAllAccountsViaCustomTypeHandler"
			resultMap="account-result">
			select * from Accounts
			order by Account_ID
		</select>	
		
		<!-- JIRA-110 -->
		<select id="GetManyAccound" resultClass="Account">
			<![CDATA[
			SELECT 
				Account_ID as Id,
				Account_FirstName as FirstName,
				Account_LastName as LastName,
				Account_Email as EmailAddress
			FROM Accounts 
			]]>
		</select>
		
		<select id="Get1Account" extends="GetManyAccound" resultClass="Account">WHERE Account_ID=1</select>
		
		<statement id="GetAccounts" resultMap="account-result"> 
			<![CDATA[SELECT * ]]> 
			<![CDATA[FROM ]]> 
			Accounts 
		</statement> 
		<!-- JIRA-110 -->
				
		<insert id="InsertAccountDynamic" parameterClass="Account">
			INSERT INTO Accounts
			(Account_ID, Account_FirstName, Account_LastName, Account_Email)
			VALUES(#Id#, #FirstName#, #LastName#
			<dynamic prepend=",">
				<isNotNull prepend="," property="EmailAddress">
					#EmailAddress#
				</isNotNull>
				<isNull prepend="," property="EmailAddress">
						null
				</isNull>
			</dynamic>
			)
           </insert>
				
		<procedure id="InsertAccountViaStoreProcedure" parameterMap="insert-params">
			ps_InsertAccount
		</procedure>
		
		<procedure id="SwapEmailAddresses" parameterMap="swap-params">
			ps_swap_email_address
		</procedure>

		<procedure id="GetAccountViaSP0" parameterMap="select-params0" resultClass="Account">
			ps_SelectAccount
		</procedure>
		
		<procedure id="GetAccountViaSP1" parameterMap="select-params1" resultClass="Account">
			ps_SelectAccount
		</procedure>
	
		<procedure id="GetAccountViaSP2" parameterMap="select-params2" resultClass="Account">
			ps_SelectAccount
		</procedure>

    <procedure id="SelectAllAccountViaSP" resultClass="Account">
      ps_SelectAllAccount
    </procedure>

    <procedure id="SelectAccountViaXML" parameterMap="XML-params" resultClass="Account">
      ps_SelectByIdList
    </procedure>
    
	</statements>

	<!-- =============================================
            OPTIONAL EXPLICIT PARAMETER MAP 
    ============================================= -->
	<parameterMaps>

    <parameterMap id="select-params1" class="int">
      <parameter property="Account_ID" column="Account_ID" />
    </parameterMap>
    
    <parameterMap id="select-params0" class ="Hashtable">
      <parameter property="Account_ID" column="Account_ID" />
    </parameterMap>

    <parameterMap id="select-params2" class="Hashtable">
      <parameter property="Account_ID" column="Account_ID" />
    </parameterMap>
    
		<parameterMap id="XML-params" class="string" >
		  <parameter property="AccountIds" column="AccountIds"/> <!-- we don't need dbType="NText" but you can specify it --> 
		</parameterMap>
    
		<parameterMap id="swap-params" class ="map" >
			<parameter property="email1" column="First_Email" />
			<parameter property="email2" column="Second_Email" />
		</parameterMap>
									
		<parameterMap id="insert-params" class ="Hashtable">
			<parameter property="Id" column="Account_ID" />
			<parameter property="FirstName" column="Account_FirstName" />
			<parameter property="LastName" column="Account_LastName" />			
			<parameter property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
			<parameter property="BannerOption" column="Account_Banner_Option"  dbType="Varchar" type="bool"/>
			<parameter property="CartOption"	column="Account_Cart_Option" typeHandler="HundredsBool"/>
		</parameterMap>
		
		<parameterMap id="update-params2" class="Account">
			<parameter property="Id" />
			<parameter property="FirstName" />
			<parameter property="LastName" />			
			<parameter property="EmailAddress" nullValue="no_email@provided.com"/>
			<parameter property="Id" />
		</parameterMap>

    <parameterMap id="update-params" class="Account">
      <parameter property="FirstName" />
      <parameter property="LastName" />
      <parameter property="EmailAddress" nullValue="no_email@provided.com"/>
      <parameter property="Id" />
    </parameterMap>
    
	</parameterMaps>
	
</sqlMap>